﻿using Cxuu.Common;
using Cxuu.Models;
using PetaPoco;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace Cxuu.Repository
{
    [AppService]
    public class ArticleRepository : BaseRepository<Article, PetaPocoContext>
    {

        public ArticleRepository(PetaPocoContext petaPocoContext) : base(petaPocoContext) { }
        /// <summary>
        /// 查询一条自己的最新信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<Article> GetByUidAsync(int userId,int cid)
        {
            Sql sql = Sql.Builder;
            sql.Select("*")
                .From("cxuu_article")
                .Where("Uid =@0 AND Cid =@1", userId,cid).OrderBy("Id desc");
            return await CreateDAO().FirstOrDefaultAsync<Article>(sql);
        }
        /// <summary>
        /// 根据CID 查询一条 用于判断当前栏目下有没有数据及查询最新一条信息内容
        /// </summary>
        /// <param name="cid"></param>
        /// <returns></returns>
        public async Task<Article> GetArticleByCidAsync(int cid)
        {
            Sql sql = Sql.Builder;
            sql.Select("*")
                .From("cxuu_article")
                .Where("Cid =@0", cid).OrderBy("Id desc");
            return await CreateDAO().FirstOrDefaultAsync<Article>(sql);
        }
        /// <summary>
        /// 编辑使用 查询一条
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<EditArticleAndContent> GetEditAsync(int? id)
        {
            Sql sql = Sql.Builder;
            sql.Select("article.*,content.*,dept.DeptName,cate.Name as CateName")
                .From("cxuu_article as article")
                .LeftJoin("cxuu_article_content as content").On("article.Id=content.Aid");
            sql.LeftJoin("cxuu_article_cate as cate").On("article.Cid = cate.Id");
            sql.LeftJoin("cxuu_admin_user user").On("article.Uid=user.Id");
            sql.LeftJoin("cxuu_admin_dept dept").On("user.DeptId=dept.Id");
            sql.Where("article.Id =@0", id);
            return await CreateDAO().FirstOrDefaultAsync<EditArticleAndContent>(sql);
        }
        /// <summary>
        /// 标记用
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<ViewArticleAndCate> GetOneForMarkAsync(int id)
        {
            Sql sql = Sql.Builder;
            sql.Select("article.*,user.Nickname")
                .From("cxuu_article as article")
                .LeftJoin("cxuu_admin_user user").On("article.Uid=user.Id");
            sql.Where("article.Id =@0", id);
            return await CreateDAO().FirstOrDefaultAsync<ViewArticleAndCate>(sql);
        }

        /// <summary>
        /// 获取列表 
        /// </summary>
        public async Task<Page<ViewArticleAndCate>> GetListAsync(ArticleQuery articleQuery)
        {
            Sql sql = Sql.Builder;

            string sqlStr = "cate.Name as CateName,dept.DeptName,user.Nickname,user.Id as Uid,mark.AdoptAppraise as Mark," +
                "article.Id,article.DocNumber,article.InsertTime,article.Title,article.Cid";
            if (articleQuery.FullTextSwitch != 1)
            {
                sql.Select(sqlStr).From("cxuu_article as article")
                .LeftJoin("cxuu_article_cate as cate").On("article.Cid = cate.Id");
            }
            else
            {
                sql.Select(sqlStr).From("cxuu_article_content as content")
                    .LeftJoin("cxuu_article article").On("content.Aid=article.Id");
                sql.LeftJoin("cxuu_article_cate as cate").On("article.Cid = cate.Id");
            }
            sql.LeftJoin("cxuu_admin_user user").On("article.Uid=user.Id");
            sql.LeftJoin("cxuu_admin_dept dept").On("user.DeptId=dept.Id");
            sql.LeftJoin("cxuu_article_mark mark").On("mark.Aid=article.Id");

            if (articleQuery.Uids == "own")
                sql.Where("article.Uid = @0", articleQuery.Uid);
            else if (articleQuery.Uids != null)
                sql.Where("article.Uid IN (" + articleQuery.Uids + ")");

            if (articleQuery.Cid != "0" && articleQuery.Cid != null)
                sql.Where("article.Cid =@0", int.Parse(articleQuery.Cid));

            if (articleQuery.FullTextSwitch != 1 && !string.IsNullOrEmpty(articleQuery.Title))
                sql.Where("article.Title like @0", "%" + articleQuery.Title + "%");

            if (articleQuery.FullTextSwitch == 1 && !string.IsNullOrEmpty(articleQuery.Title))
                sql.Where("content.Content like @0", "%" + articleQuery.Title + "%");

            if (!string.IsNullOrEmpty(articleQuery.NickName))
                sql.Where("user.Nickname like @0", "%" + articleQuery.NickName + "%");

            if (articleQuery.MarkStatus == 1)
                sql.Where("mark.Status =@0", articleQuery.MarkStatus);

            if (articleQuery.StartDate != null && articleQuery.EndDate != null)
                sql.Where("article.InsertTime between @0 AND @1", articleQuery.StartDate, articleQuery.EndDate);

            if (!string.IsNullOrEmpty(articleQuery.Orderby))
                sql.OrderBy("article." + articleQuery.Orderby);

            return await CreateDAO().PageAsync<ViewArticleAndCate>(articleQuery.CurrentPage, articleQuery.Limit, sql);
        }


        /// <summary>
        /// 插入一条信息
        /// </summary>
        /// <param name="article"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public async Task<Article> InsertArticleAndContentAsync(Article article, ArticleContent content)
        {
            var dao = CreateDAO();
            await dao.OpenSharedConnectionAsync();
            try
            {
                var a = await dao.InsertAsync(article);

                if (content.Content != null)
                {
                    Sql sql = Sql.Builder;
                    sql.Append("INSERT INTO cxuu_article_content (Aid, Content) VALUES (@0,@1);", article.Id, content.Content);
                    await CreateDAO().ExecuteAsync(sql);
                }

                if (a != null)
                    return article;
            }
            finally
            {
                dao.CloseSharedConnection();
            }
            return null;
        }
        /// <summary>
        /// 更新一条信息
        /// </summary>
        /// <param name="article"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public async Task<int> UpdateArticleAndContentAsync(Article article, ArticleContent content)
        {
            if (article != null)
            {
                var dao = CreateDAO();
                if (content != null)
                {
                    ArticleContent ac = await dao.SingleOrDefaultAsync<ArticleContent>(article.Id);
                    if (ac != null)
                    {
                        ac.Content = content.Content;
                        await dao.UpdateAsync(ac);
                    }
                    else
                    {
                        Sql sql = Sql.Builder;
                        sql.Append("INSERT INTO cxuu_article_content (Aid, Content) VALUES (@0,@1);", article.Id, content.Content);
                        await CreateDAO().ExecuteAsync(sql);
                    }
                }
                Article a = await dao.SingleOrDefaultAsync<Article>(article.Id);
                a.Cid = article.Cid;
                a.Title = article.Title;
                a.Attments = article.Attments;
                a.Miji = article.Miji;
                a.GreatTime = article.GreatTime;
                // a.DocNumber = article.DocNumber;
                a.Department = article.Department;
                a.Leader = article.Leader;
                a.Examine = article.Examine;
                a.Auther = article.Auther;
                a.Proofread = article.Proofread;
                a.EarlierExa = article.EarlierExa;
                a.Remarks = article.Remarks;
                return await dao.UpdateAsync(a);
            }
            return 0;
        }

        /// <summary>
        /// 按栏目统计发布的信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<ArticleCount>> CountArticleForMonthAsync()
        {

            Sql sql = Sql.Builder;
            sql.Select("COUNT(1) AS value, article.cid AS cids,cate.name AS name")
               .From("cxuu_article as article")
               .LeftJoin("cxuu_article_cate as cate").On("article.cid = cate.Id");
            sql.Where("DATE_FORMAT(article.InsertTime,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')");
            sql.GroupBy("cids asc");

            return await CreateDAO().FetchAsync<ArticleCount>(sql);
        }

        /// <summary>
        /// 按用户统计发布的信息量 排名
        /// </summary>
        /// <returns></returns>
        public async Task<List<ArticleCount>> CountArticleForUserAsync(bool isMonth = true)
        {
            Sql sql = Sql.Builder;
            sql.Select("COUNT(1) AS Value, article.Uid AS uids,user.Nickname AS name ,SUM(case when mark.Aid=article.Id then 1 else 0 end) as MarkNum")
                .From("cxuu_article as article")
                .LeftJoin("cxuu_admin_user as user").On("article.Uid = user.Id");
            sql.LeftJoin("cxuu_article_mark as mark").On("article.Id = mark.Aid");
            if (isMonth)
                sql.Where("DATE_FORMAT(article.InsertTime,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')");
            else
                sql.Where("DATE_FORMAT(article.InsertTime,'%Y') = DATE_FORMAT(NOW(),'%Y')");
            //sql.OrderBy("value desc");
            sql.Where("user.Id !=1 ");

            sql.GroupBy("uids asc");

            return await CreateDAO().FetchAsync<ArticleCount>(sql);
        }

        /// <summary>
        /// 按角色统计本月发布的信息量
        /// </summary>
        /// <returns></returns>
        public async Task<List<ArticleCount>> CountUserGroupYearAsync()
        {
            Sql sql = Sql.Builder;
            sql.Select("COUNT(1) AS value, article.Uid AS dids,dept.DeptName AS name")
                .From("cxuu_article as article")
                .LeftJoin("cxuu_admin_user as user").On("article.Uid = user.Id");
            sql.LeftJoin("cxuu_admin_dept as dept").On("user.DeptId = dept.Id");
            sql.Where("DATE_FORMAT(article.InsertTime,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m')");
            sql.GroupBy("dids asc");
            return await CreateDAO().FetchAsync<ArticleCount>(sql);
        }

        /// <summary>
        /// 统计信息总量
        /// </summary>
        /// <returns></returns>
        public async Task<NumCount> CountAllAsync()
        {
            NumCount numCount = new()
            {
                Article = await CreateDAO().ExecuteScalarAsync<long>("SELECT count(1) FROM cxuu_article"),
                User = await CreateDAO().ExecuteScalarAsync<long>("SELECT count(1) FROM cxuu_admin_user"),
                Docreceived = await CreateDAO().ExecuteScalarAsync<long>("SELECT count(1) FROM cxuu_article_docreceived"),
                Statement = await CreateDAO().ExecuteScalarAsync<long>("SELECT count(1) FROM cxuu_article_statement"),
                Log = await CreateDAO().ExecuteScalarAsync<long>("SELECT count(1) FROM cxuu_log_sql"),
                Notice = await CreateDAO().ExecuteScalarAsync<long>("SELECT count(1) FROM cxuu_notice_key"),
            };
            return numCount;
        }


        /// <summary>
        /// 查询统计 
        /// </summary>
        public async Task<List<Statistics>> StatisticsAsync(StatisticsQuery articleQuery)
        {
            string typeString = articleQuery.SearchType switch
            {
                "month" => ",MONTH(`InsertTime`) AS name",
                "year" => ",YEAR(`InsertTime`) AS name",
                "category" => ",cate.Name AS name",
                _ => ",WEEK(`InsertTime`) AS name",
            };
            Sql sql = Sql.Builder;
            sql.Select("COUNT(1) AS value " + typeString + ",SUM(case when mark.Aid=article.Id then 1 else 0 end) as MarkNum")
                .From("cxuu_article as article")
                .LeftJoin("cxuu_admin_user as user").On("article.Uid = user.Id");
            sql.LeftJoin("cxuu_article_mark as mark").On("article.Id = mark.Aid");

            if (articleQuery.SearchType == "category")
                sql.LeftJoin("cxuu_article_cate as cate").On("article.Cid = cate.Id");

            if (articleQuery.SearchCid != 0)
                sql.Where("article.Cid =@0", articleQuery.SearchCid);

            if (!string.IsNullOrEmpty(articleQuery.NickName))
                sql.Where("user.Nickname like @0", "%" + articleQuery.NickName + "%");


            if (articleQuery.StartDate != null && articleQuery.EndDate != null)
                sql.Where("article.InsertTime between @0 AND @1", articleQuery.StartDate, articleQuery.EndDate);

            sql.Where("user.Id !=1 ");

            sql.GroupBy("name asc");

            return await CreateDAO().FetchAsync<Statistics>(sql);

        }

    }
}
